package com.lms.mapper;

import com.lms.pojo.BorrowRecord;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class BorrowRecordDAO {

    /**
     * 添加借阅记录到数据库中
     *
     * @param borrowRecord UserID, BookID, BorrowDate, ReturnDate不为null
     * @return 是否添加成功
     * @implNote INSERT INTO BorrowRecord (UserID, BookID, BorrowDate, ReturnDate) VALUES (?, ?, ?, ?)
     */
    public boolean add(BorrowRecord borrowRecord) {
        boolean ret = false;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
//            String sql = "INSERT INTO BorrowRecord (UserID, BookID, BorrowDate, ReturnDate, ActualReturnDate) VALUES (?, ?, ?, ?, ?)";
            String sql = "INSERT INTO BorrowRecord (UserID, BookID, BorrowDate, ReturnDate) VALUES (?, ?, ?, ?)";

            PreparedStatement statement = sqlConnect.prepareStatement(sql);
            statement.setInt(1, borrowRecord.getUserID());
            statement.setInt(2, borrowRecord.getBookID());
            statement.setDate(3, new java.sql.Date(borrowRecord.getBorrowDate().getTime()));
            statement.setDate(4, new java.sql.Date(borrowRecord.getReturnDate().getTime()));

//            statement.setDate(5, new java.sql.Date(borrowRecord.getActualReturnDate().getTime()));


            int rowsInserted = statement.executeUpdate();
            if (rowsInserted > 0) {
                ret = true; // 插入成功
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null)
                sqlConnect.close();
        }

        return ret;
    }

    /**
     * 修改借阅记录
     *
     * @param borrowRecord 所有属性都不为null
     * @return 是否成功
     * @implNote UPDATE BorrowRecord SET UserID = ?, BookID = ?, BorrowDate = ?, ReturnDate = ?, ActualReturnDate = ? WHERE RecordID = ?
     */
    public boolean update(BorrowRecord borrowRecord) {
        boolean ret = false;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "UPDATE BorrowRecord SET UserID = ?, BookID = ?, BorrowDate = ?, ReturnDate = ?, ActualReturnDate = ? WHERE RecordID = ?";
            PreparedStatement statement = sqlConnect.prepareStatement(sql);
            statement.setInt(1, borrowRecord.getUserID());
            statement.setInt(2, borrowRecord.getBookID());
            statement.setDate(3, new java.sql.Date(borrowRecord.getBorrowDate().getTime()));
            statement.setDate(4, new java.sql.Date(borrowRecord.getReturnDate().getTime()));
            statement.setDate(5, new java.sql.Date(borrowRecord.getActualReturnDate().getTime()));
            statement.setInt(6, borrowRecord.getRecordID());

            int rowsUpdated = statement.executeUpdate();
            if (rowsUpdated > 0) {
                ret = true; // 更新成功
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null)
                sqlConnect.close();
        }

        return ret;
    }

    /**
     * 根据 RecordID 设置 ActualReturnDate
     *
     * @param borrowRecord
     * @return 是否成功
     * @implNote UPDATE BorrowRecord SET ActualReturnDate = ? WHERE RecordID = ?
     */
    public boolean updateActualReturnDate(BorrowRecord borrowRecord) {
        boolean ret = false;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "UPDATE BorrowRecord SET ActualReturnDate = ? WHERE RecordID = ?";
            PreparedStatement statement = sqlConnect.prepareStatement(sql);
            statement.setDate(1, new java.sql.Date(borrowRecord.getActualReturnDate().getTime()));
            statement.setInt(2, borrowRecord.getRecordID());

            int rowsUpdated = statement.executeUpdate();
            if (rowsUpdated > 0) {
                ret = true; // 更新成功
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null)
                sqlConnect.close();
        }

        return ret;
    }

    /**
     * 删除借阅记录
     *
     * @param borrowRecord
     * @return 是否成功
     * @implNote DELETE FROM BorrowRecord WHERE RecordID = ?
     */
    public boolean delete(BorrowRecord borrowRecord) {
        boolean ret = false;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "DELETE FROM BorrowRecord WHERE RecordID = ?";
            PreparedStatement statement = sqlConnect.prepareStatement(sql);
            statement.setInt(1, borrowRecord.getRecordID());

            int rowsDeleted = statement.executeUpdate();
            if (rowsDeleted > 0) {
                ret = true; // 删除成功
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null) {
                sqlConnect.close();
            }
        }

        return ret;
    }

    /**
     * 查询借阅记录(分页）
     *
     * @param pageNo 第几页
     * @param pageSize 每一页的数量
     * @return 对应页数的数据
     */
    public List<BorrowRecord> find(int pageNo, int pageSize) {
        List<BorrowRecord> lists = new ArrayList<>();

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "SELECT * FROM BorrowRecord LIMIT ?, ?";
            PreparedStatement statement = sqlConnect.prepareStatement(sql);
            int offset = (pageNo - 1) * pageSize;
            statement.setInt(1, offset);
            statement.setInt(2, pageSize);

            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                BorrowRecord borrowRecord = new BorrowRecord();
                borrowRecord.setRecordID(resultSet.getInt("RecordID"));
                borrowRecord.setUserID(resultSet.getInt("UserID"));
                borrowRecord.setBookID(resultSet.getInt("BookID"));
                borrowRecord.setBorrowDate(resultSet.getDate("BorrowDate"));
                borrowRecord.setReturnDate(resultSet.getDate("ReturnDate"));
                borrowRecord.setActualReturnDate(resultSet.getDate("ActualReturnDate"));

                lists.add(borrowRecord);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null) {
                sqlConnect.close();
            }
        }

        return lists;
    }

    /**
     * 返回总页数
     *
     * @param pageSize
     * @return 返回总页数
     */
    public int getTotalPages(int pageSize) {
        int totalPages = 0;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String countSql = "SELECT COUNT(*) FROM BorrowRecord";
            PreparedStatement countStatement = sqlConnect.prepareStatement(countSql);

            ResultSet countResultSet = countStatement.executeQuery();
            if (countResultSet.next()) {
                int totalRecords = countResultSet.getInt(1);
                totalPages = (int) Math.ceil((double) totalRecords / pageSize);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null) {
                sqlConnect.close();
            }
        }

        return totalPages;
    }

    /**
     * 通过id查询借阅记录
     * @param id 指定的id
     * @return 借阅记录
     */
    public BorrowRecord findById(int id) {
        BorrowRecord ret = null;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "SELECT * FROM BorrowRecord WHERE RecordID = ?";
            PreparedStatement statement = sqlConnect.prepareStatement(sql);
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                ret = new BorrowRecord();
                ret.setRecordID(resultSet.getInt("RecordID"));
                ret.setUserID(resultSet.getInt("UserID"));
                ret.setBookID(resultSet.getInt("BookID"));
                ret.setBorrowDate(resultSet.getDate("BorrowDate"));
                ret.setReturnDate(resultSet.getDate("ReturnDate"));
                ret.setActualReturnDate(resultSet.getDate("ActualReturnDate"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null) {
                sqlConnect.close();
            }
        }

        return ret;
    }

    /**
     * 根据筛选条件查询借阅记录（分页）
     *
     * @param pageNo   当前页码
     * @param pageSize 每页记录数
     * @param filter   借阅记录的筛选器
     * @return 符合筛选条件的借阅记录列表
     */
    public List<BorrowRecord> findByFilter(int pageNo, int pageSize, BorrowRecordFilter filter) {
        List<BorrowRecord> lists = new ArrayList<>();

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "SELECT * FROM BorrowRecord WHERE " + filter.generateFilterCondition() + " LIMIT ?, ?";
            PreparedStatement statement = sqlConnect.prepareStatement(sql);

            // 设置筛选条件中的参数值
            Object[] parameters = filter.getParameters();
            for (int i = 0; i < parameters.length; i++) {
                statement.setObject(i + 1, parameters[i]);
            }

            //设置LIMIT
            int offset = (pageNo - 1) * pageSize;
            statement.setInt(parameters.length + 1, offset);
            statement.setInt(parameters.length + 2, pageSize);

            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                BorrowRecord borrowRecord = new BorrowRecord();
                borrowRecord.setRecordID(resultSet.getInt("RecordID"));
                borrowRecord.setUserID(resultSet.getInt("UserID"));
                borrowRecord.setBookID(resultSet.getInt("BookID"));
                borrowRecord.setBorrowDate(resultSet.getDate("BorrowDate"));
                borrowRecord.setReturnDate(resultSet.getDate("ReturnDate"));
                borrowRecord.setActualReturnDate(resultSet.getDate("ActualReturnDate"));

                lists.add(borrowRecord);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null) {
                sqlConnect.close();
            }
        }

        return lists;
    }

    /**
     * 获取通过筛选器查询的借阅记录页数
     * @param pageSize 每页的数据数量
     * @param filter 借阅记录的筛选器
     * @return 借阅记录
     */
    public int getTotalPages(int pageSize, BorrowRecordFilter filter) {
        int totalPages = 0;

        SQLConnect sqlConnect = null;
        try {
            sqlConnect = new SQLConnect();
            String sql = "SELECT COUNT(*) FROM BorrowRecord WHERE " + filter.generateFilterCondition();
            PreparedStatement statement = sqlConnect.prepareStatement(sql);

            // 设置筛选条件中的参数值
            Object[] parameters = filter.getParameters();
            for (int i = 0; i < parameters.length; i++) {
                statement.setObject(i + 1, parameters[i]);
            }

            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                int totalRecords = resultSet.getInt(1);
                totalPages = (int) Math.ceil((double) totalRecords / pageSize);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sqlConnect != null) {
                sqlConnect.close();
            }
        }

        return totalPages;
    }

}
